package co.gov.medellin.ssti.sub.model.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.LinkedList;
import java.util.List;

import co.gov.medellin.ssti.sub.model.dao.IRepBeneficioOtorgadoDAO;
import co.gov.medellin.ssti.sub.model.dao.exception.DAOException;
import co.gov.medellin.ssti.sub.model.vo.ProgramaVO;
import co.gov.medellin.ssti.sub.model.vo.RepAtributoVO;
import co.gov.medellin.ssti.sub.model.vo.RepBeneficioOtorgadoVO;
import co.gov.medellin.ssti.sub.model.vo.SubprogramaVO;
import co.gov.medellin.ssti.sub.model.vo.VflexVO;

public class RepBeneficioOtorgadoDAOImpl extends BaseDAOImpl implements
		IRepBeneficioOtorgadoDAO {

	public RepBeneficioOtorgadoDAOImpl(Connection con) {
		super(con);
	}

	public Collection<RepBeneficioOtorgadoVO> findBeneficioOtorgadoByCriteria(
			RepBeneficioOtorgadoVO criteria) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		List<Object> parameters = new ArrayList<Object>();
		StringBuffer query = new StringBuffer(
				"SELECT COUNT(beneotor.codigo) cantidad_otorgada, sede.CODIGO codsede, "
						+ " sede.NOMBRE nomsede, prog.CODIGO codpro, prog.NOMBRE nompro, "
						+ " subpro.CODIGO codsubpro, subpro.NOMBRE nomsubpro "
						+ " FROM TCS_BEN_PROGRAMA prog, TCS_BEN_SUBPROGRAMA subpro, "
						+ " TCS_BEN_BENEFICIO_OTORGADO beneotor, TCS_BEN_SEDE sede "
						+ " WHERE beneotor.SBPR_CODIGO =  subpro.CODIGO "
						+ " AND beneotor.SEDE_CODIGO = sede.CODIGO AND subpro.PRG_CODIGO = prog.CODIGO "
						+ " AND beneotor.FECHA_BENEFICIO <= sysdate AND prog.vigencia='A' AND subpro.vigencia='A' ");

		if ((Object) criteria.getCodeSede() != null) {
			query.append(" AND sede.CODIGO = ? ");
			parameters.add(criteria.getCodeSede());
		}
		if ((Object) criteria.getCodeSubprograma() != null) {
			query.append(" AND subpro.CODIGO = ? ");
			parameters.add(criteria.getCodeSubprograma());
		}
		if ((Object) criteria.getCodePrograma() != null) {
			query.append(" AND prog.CODIGO = ? ");
			parameters.add(criteria.getCodePrograma());
		}

		if ((Object) criteria.getDesde() != null
				&& (Object) criteria.getHasta() != null) {
			query.append(" AND beneotor.FECHA_BENEFICIO BETWEEN TO_DATE(?,'dd/mm/yyyy') AND TO_DATE(?,'dd/mm/yyyy')");
			parameters.add(criteria.getDesde());
			parameters.add(criteria.getHasta());
		}

		if ((Object) criteria.getGestor() != null
				&& criteria.getCodigoAtrGestor() != null) {
			query.append(" AND Fcs_Ben_Valor_Atributo(?, beneotor.CODIGO, beneotor.BEN_CODIGO, beneotor.FECHA_BENEFICIO) = ? ");
			parameters.add(criteria.getCodigoAtrGestor());
			parameters.add(criteria.getGestor());
		}
		query.append(" GROUP BY sede.CODIGO, sede.NOMBRE, prog.CODIGO, prog.NOMBRE, subpro.CODIGO, "
				+ "subpro.NOMBRE");

		query.append(" ORDER BY prog.NOMBRE,subpro.NOMBRE,sede.NOMBRE ");

		ArrayList<RepBeneficioOtorgadoVO> lbeneotor = new ArrayList<RepBeneficioOtorgadoVO>();
		try {
			pstmt = conn.prepareStatement(query.toString());
			pstmt = setQueryParameters(pstmt, parameters);
			rst = pstmt.executeQuery();

			RepBeneficioOtorgadoVO beneotorVO = null;
			while (rst.next()) {
				beneotorVO = new RepBeneficioOtorgadoVO();
				beneotorVO.setCodeSede(new Long(rst.getLong("codsede")));
				beneotorVO.setNombreSede(rst.getString("nomsede"));
				beneotorVO.setCodePrograma(new Long(rst.getLong("codpro")));
				beneotorVO.setNombrePrograma(rst.getString("nompro"));
				beneotorVO
						.setCodeSubprograma(new Long(rst.getLong("codsubpro")));
				beneotorVO.setNombreSubprograma(rst.getString("nomsubpro"));
				beneotorVO.setDesde(criteria.getDesde());
				beneotorVO.setHasta(criteria.getHasta());
				beneotorVO.setCantidad(rst.getString("cantidad_otorgada"));
				lbeneotor.add(beneotorVO);
				beneotorVO = null;
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return lbeneotor;
	}

	public Collection<RepBeneficioOtorgadoVO> findBeneficioOtorgadoConGestorByCriteria(
			RepBeneficioOtorgadoVO criteria) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		List<Object> parameters = new ArrayList<Object>();
		StringBuffer query = new StringBuffer(
				"SELECT COUNT(beneotor.codigo) cantidad_otorgada, sede.CODIGO codsede, "
						+ " sede.NOMBRE nomsede, prog.CODIGO codpro, prog.NOMBRE nompro, "
						+ " subpro.CODIGO codsubpro, subpro.NOMBRE nomsubpro "
						+ " FROM TCS_BEN_PROGRAMA prog, TCS_BEN_SUBPROGRAMA subpro, "
						+ " TCS_BEN_BENEFICIO_OTORGADO beneotor, TCS_BEN_SEDE sede, TCS_BEN_SUBP_ATR_BENEFICIARIO sab "
						+ " WHERE beneotor.SBPR_CODIGO =  subpro.CODIGO "
						+ " AND beneotor.SEDE_CODIGO = sede.CODIGO AND subpro.PRG_CODIGO = prog.CODIGO "
						+ " AND beneotor.FECHA_BENEFICIO <= sysdate AND prog.vigencia='A' AND subpro.vigencia='A' "
						+ " AND sab.sbpr_codigo = subpro.codigo AND UPPER(TRIM(sab.NOMBRE)) = 'GESTOR' ");

		if ((Object) criteria.getCodeSede() != null) {
			query.append(" AND sede.CODIGO = ? ");
			parameters.add(criteria.getCodeSede());
		}
		if ((Object) criteria.getCodeSubprograma() != null) {
			query.append(" AND subpro.CODIGO = ? ");
			parameters.add(criteria.getCodeSubprograma());
		}
		if ((Object) criteria.getCodePrograma() != null) {
			query.append(" AND prog.CODIGO = ? ");
			parameters.add(criteria.getCodePrograma());
		}
		if ((Object) criteria.getDesde() != null
				&& (Object) criteria.getHasta() != null) {
			query.append(" AND beneotor.FECHA_BENEFICIO between to_date(?,'dd/mm/yyyy') and to_date(?,'dd/mm/yyyy')");
			parameters.add(criteria.getDesde());
			parameters.add(criteria.getHasta());
		}
		if ((Object) criteria.getGestor() != null
				&& criteria.getCodigoAtrGestor() != null) {
			query.append(" AND Fcs_Ben_Valor_Atributo(?, beneotor.CODIGO, beneotor.BEN_CODIGO, beneotor.FECHA_BENEFICIO) = ? ");
			parameters.add(criteria.getCodigoAtrGestor());
			parameters.add(criteria.getGestor());
		}
		query.append(" GROUP BY sede.CODIGO, sede.NOMBRE, prog.CODIGO, prog.NOMBRE, subpro.CODIGO, "
				+ "subpro.NOMBRE");
		query.append(" ORDER BY prog.NOMBRE,subpro.NOMBRE,sede.NOMBRE ");

		ArrayList<RepBeneficioOtorgadoVO> lbeneotor = new ArrayList<RepBeneficioOtorgadoVO>();
		try {
			pstmt = conn.prepareStatement(query.toString());
			pstmt = setQueryParameters(pstmt, parameters);
			rst = pstmt.executeQuery();

			RepBeneficioOtorgadoVO beneotorVO = null;
			while (rst.next()) {
				beneotorVO = new RepBeneficioOtorgadoVO();
				beneotorVO.setCodeSede(new Long(rst.getLong("codsede")));
				beneotorVO.setNombreSede(rst.getString("nomsede"));
				beneotorVO.setCodePrograma(new Long(rst.getLong("codpro")));
				beneotorVO.setNombrePrograma(rst.getString("nompro"));
				beneotorVO
						.setCodeSubprograma(new Long(rst.getLong("codsubpro")));
				beneotorVO.setNombreSubprograma(rst.getString("nomsubpro"));
				beneotorVO.setDesde(criteria.getDesde());
				beneotorVO.setHasta(criteria.getHasta());
				beneotorVO.setCantidad(rst.getString("cantidad_otorgada"));
				lbeneotor.add(beneotorVO);
				beneotorVO = null;
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return lbeneotor;
	}

	public Collection<VflexVO> findListaAtri(Long codeAtri) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String query = "SELECT vfl.DESCRIPCION descrip, vfl.CODIGO_VALOR codvalor "
				+ " FROM TCS_BEN_SUBP_ATR_BENEFICIARIO atri, "
				+ " paoyer.TCS_TIPO_VALOR_FLEX tvfl, paoyer.TCS_VALOR_FLEXIBLE vfl "
				+ " WHERE atri.CODIGO = ? "
				+ " AND atri.TTVF_CODIGO_DOMINIO = tvfl.CODIGO "
				+ " AND tvfl.CODIGO = vfl.TVF_CODIGO AND vfl.VIGENCIA='S'";

		ArrayList<VflexVO> lista = new ArrayList<VflexVO>();
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setLong(1, codeAtri.longValue());
			rst = pstmt.executeQuery();
			while (rst.next()) {
				VflexVO vflVO = new VflexVO();
				vflVO.setDescVflex(rst.getString("descrip"));
				vflVO.setCodigoVflex(rst.getString("codvalor"));
				vflVO.setNombreVflex(rst.getString("codvalor") + "~"
						+ rst.getString("descrip"));
				lista.add(vflVO);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return lista;
	}

	public Collection<RepAtributoVO> findAtributoEspecificos(
			Long codeSubprograma) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		Collection<RepAtributoVO> listaAtriEsp = new LinkedList<RepAtributoVO>();

		String query = "SELECT CODIGO, TIPO_ATRIBUTO, NOMBRE, nvl(REST_CODIGO,'0') REST_CODIGO, ORDEN, LONGITUD, "
				+ " OBLIGATORIO, ETIQUETA, VALOR_SUGERIDO, VALIDAR_LISTA, VIGENCIA, "
				+ " DESCRIPCION, TIPO_DATO, TTVF_CODIGO_DOMINIO "
				+ " FROM TCS_BEN_SUBP_ATR_BENEFICIARIO WHERE SBPR_CODIGO = ? "
				+ " AND VIGENCIA = 'A' ORDER BY ORDEN";

		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setLong(1, codeSubprograma.longValue());
			rst = pstmt.executeQuery();
			while (rst.next()) {
				RepAtributoVO repatriVO = new RepAtributoVO();
				repatriVO.setCodigo(new Long(rst.getString("CODIGO")));
				repatriVO.setEtiqueta(rst.getString("ETIQUETA"));
				repatriVO.setOrigen("E");
				listaAtriEsp.add(repatriVO);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return listaAtriEsp;
	}

	public Collection<ProgramaVO> findProgramaByUserReport(Long codeUser,
			String userGroupName, Long codeArea) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		ProgramaVO criteria = new ProgramaVO();
		List<Object> parameters = new ArrayList<Object>();
		StringBuffer query = new StringBuffer(
				"SELECT DISTINCT pro.CODIGO COD_PROGRAMA, pro.NOMBRE NOM_PROGRAMA, pro.DESCRIPCION DESC_PROGRAMA, "
						+ " pro.OBJETIVO, pro.POBLACION_OBJETIVO, area.CODIGO COD_AREA, area.NOMBRE NOM_AREA, "
						+ " usu.CODIGO COD_USUARIO, usu.NOMBRE NOM_USUARIO, a.admProg "
						+ " FROM VCS_BEN_SUBPROG_ALLOWED a, "
						+ " TCS_BEN_PROGRAMA pro, TCS_BEN_AREA area, paoyer.TCS_USUARIO usu, "
						+ " paoyer.tcs_grupo_usuario gus, TCS_BEN_SUBPROGRAMA_USUARIO subus "
						+ " WHERE  gus.CODIGO = usu.GUS_CODIGO "
						+ " AND a.COD_PROGRAMA = pro.CODIGO "
						+ " AND pro.AREA_CODIGO = area.CODIGO "
						+ " AND pro.USU_CODIGO = usu.CODIGO "
						+ " AND pro.VIGENCIA = 'A' ");

		if ((Object) codeUser != null) {
			query.append("AND ('ADMINISTRADOR GENERAL' = ? OR 'SUPER USUARIO' = ? "
					+ " OR admSubpro = ? OR pro.USU_CODIGO = ? "
					+ " OR a.permisos = ? ");
			parameters.add(userGroupName);
			parameters.add(userGroupName);
			parameters.add(codeUser);
			parameters.add(codeUser);
			parameters.add(codeUser);
			if (userGroupName != null
					&& userGroupName.equals("ADMINISTRADOR SECRETARIA")) {
				query.append(" OR COD_AREA = ? ");
				parameters.add(codeArea);
			}
			query.append(" ) ");
		} else {
			return null;
		}

		if (criteria.getCode() != null) {
			query.append(" AND COD_PROGRAMA = ? ");
			parameters.add(criteria.getCode());
		}
		if (!criteria.getNombre().equalsIgnoreCase("")) {
			query.append(" AND NOM_PROGRAMA LIKE ? ");
			parameters.add("%" + criteria.getNombre().toUpperCase().trim()
					+ "%");
		}
		if (criteria.getCodeArea() != null) {
			query.append(" AND COD_AREA = ? ");
			parameters.add(criteria.getCodeArea());
		}
		query.append(" ORDER BY NOM_PROGRAMA");
//		System.out.println(query.toString());
//		for (Object s : parameters) {
//			System.out.println(s.toString());
//		}
		ArrayList<ProgramaVO> programas = new ArrayList<ProgramaVO>();
		try {
			pstmt = conn.prepareStatement(query.toString());
			pstmt = setQueryParameters(pstmt, parameters);
			rst = pstmt.executeQuery();

			while (rst.next()) {
				ProgramaVO pro = new ProgramaVO();
				pro.setCode(new Long(rst.getLong("COD_PROGRAMA")));
				pro.setNombre(rst.getString("NOM_PROGRAMA"));
				pro.setDescripcion(rst.getString("DESC_PROGRAMA"));
				pro.setObjetivo(rst.getString("OBJETIVO"));
				pro.setPoblacionObjetivo(rst.getString("POBLACION_OBJETIVO"));
				pro.setCodeArea(new Long(rst.getString("COD_AREA")));
				pro.setNombreArea(rst.getString("NOM_AREA"));
				pro.setCodeUsuario(new Long(rst.getString("COD_USUARIO")));
				pro.setNombreUsuario(rst.getString("NOM_USUARIO"));
				programas.add(pro);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return programas;
	}

	public Collection<ProgramaVO> findProgramaWithGestorByUserReport(
			Long codeUser, String userGroupName, Long codeArea)
			throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		ProgramaVO criterea = new ProgramaVO();
		List<Object> parameters = new ArrayList<Object>();
		StringBuffer query = new StringBuffer(
				"SELECT DISTINCT pro.CODIGO COD_PROGRAMA, pro.NOMBRE NOM_PROGRAMA, pro.DESCRIPCION DESC_PROGRAMA, "
						+ "pro.OBJETIVO, pro.POBLACION_OBJETIVO, area.CODIGO COD_AREA, area.NOMBRE NOM_AREA, "
						+ "usu.CODIGO COD_USUARIO, usu.NOMBRE NOM_USUARIO, a.admProg "
						+ "FROM VCS_BEN_SUBPROG_ALLOWED a, "
						+ "TCS_BEN_PROGRAMA pro, TCS_BEN_AREA area, paoyer.TCS_USUARIO usu, TCS_BEN_SUBPROGRAMA sub, "
						+ "TCS_BEN_SUBP_ATR_BENEFICIARIO sab, paoyer.tcs_grupo_usuario gus, TCS_BEN_SUBPROGRAMA_USUARIO subus "
						+ "WHERE  gus.CODIGO = usu.GUS_CODIGO "
						+ "AND a.COD_PROGRAMA = pro.CODIGO "
						+ "AND pro.AREA_CODIGO = area.CODIGO "
						+ "AND pro.USU_CODIGO = usu.CODIGO "
						+ "AND pro.VIGENCIA = 'A' "
						+ "AND sub.prg_codigo = pro.codigo "
						+ "AND sab.SBPR_CODIGO = sub.codigo "
						+ "AND UPPER(TRIM(sab.nombre)) = 'GESTOR' ");

		if ((Object) codeUser != null) {
			query.append("AND ('ADMINISTRADOR GENERAL' = ? OR 'SUPER USUARIO' = ? "
					+ " OR admSubpro = ? OR pro.USU_CODIGO = ? "
					+ " OR a.permisos = ? ");
			parameters.add(userGroupName);
			parameters.add(userGroupName);
			parameters.add(codeUser);
			parameters.add(codeUser);
			parameters.add(codeUser);
			if (userGroupName != null
					&& userGroupName.equals("ADMINISTRADOR SECRETARIA")) {
				query.append(" OR COD_AREA = ? ");
				parameters.add(codeArea);
			}
			query.append(" ) ");
		} else {
			return null;
		}

		if (criterea.getCode() != null) {
			query.append("AND COD_PROGRAMA = ? ");
			parameters.add(criterea.getCode());
		}
		if (!criterea.getNombre().equalsIgnoreCase("")) {
			query.append("AND NOM_PROGRAMA LIKE ? ");
			parameters.add("%" + criterea.getNombre().toUpperCase().trim()
					+ "%");
		}
		if (criterea.getCodeArea() != null) {
			query.append("AND COD_AREA = ? ");
			parameters.add(criterea.getCodeArea());
		}
		query.append(" ORDER BY NOM_PROGRAMA");

		ArrayList<ProgramaVO> programas = new ArrayList<ProgramaVO>();
		try {
			pstmt = conn.prepareStatement(query.toString());
			pstmt = setQueryParameters(pstmt, parameters);
			rst = pstmt.executeQuery();
			ProgramaVO pro = null;
			while (rst.next()) {
				pro = new ProgramaVO();
				pro.setCode(new Long(rst.getLong("COD_PROGRAMA")));
				pro.setNombre(rst.getString("NOM_PROGRAMA"));
				pro.setDescripcion(rst.getString("DESC_PROGRAMA"));
				pro.setObjetivo(rst.getString("OBJETIVO"));
				pro.setPoblacionObjetivo(rst.getString("POBLACION_OBJETIVO"));
				pro.setCodeArea(new Long(rst.getString("COD_AREA")));
				pro.setNombreArea(rst.getString("NOM_AREA"));
				pro.setCodeUsuario(new Long(rst.getString("COD_USUARIO")));
				pro.setNombreUsuario(rst.getString("NOM_USUARIO"));
				programas.add(pro);
				pro = null;
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return programas;
	}

	public Collection<SubprogramaVO> findPermissionsReport(Long codeUser,
			String userGroupName, Long codePrograma, Long codeArea)
			throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String query = "SELECT DISTINCT pro.CODIGO codprog,pro.NOMBRE nompro,subpro.codigo codeSubprograma, "
				+ " UPPER(subpro.nombre) nomSubprograma, usu.codigo,subpro.usu_codigo,pro.usu_codigo "
				+ " FROM TCS_BEN_SUBPROGRAMA subpro, "
				+ " TCS_BEN_PROGRAMA pro, "
				+ " TCS_BEN_SUBPROGRAMA_USUARIO subus, "
				+ " paoyer.tcs_usuario usu "
				+ " WHERE pro.codigo = subpro.PRG_CODIGO "
				+ " AND (subpro.CODIGO = subus.SBPR_CODIGO OR 'ADMINISTRADOR GENERAL' = ? "
				+ " OR 'SUPER USUARIO' = ? "
				+ " OR subpro.usu_codigo = ? OR pro.usu_codigo = ?) "
				+ " AND subus.USU_CODIGO = usu.CODIGO "
				+ " AND usu.CODIGO = ? " + " ORDER BY nompro";

		ArrayList<SubprogramaVO> subprogramas = new ArrayList<SubprogramaVO>();
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, userGroupName);
			pstmt.setString(2, userGroupName);
			pstmt.setLong(3, codeUser.longValue());
			pstmt.setLong(4, codeUser.longValue());
			pstmt.setLong(5, codeUser.longValue());
			rst = pstmt.executeQuery();

			SubprogramaVO subpro = null;
			while (rst.next()) {
				subpro = new SubprogramaVO();
				subpro.setCode(new Long(rst.getLong("codeSubprograma")));
				subpro.setNombre(rst.getString("nomSubprograma"));
				subpro.setNombrePrograma(rst.getString("nompro"));
				subprogramas.add(subpro);
				subpro = null;
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return subprogramas;
	}

	/*
	 * public Collection findSubProgramaByUserReport(Long codeUser, String
	 * userGroupName, String codeprograma, Long codeArea) throws DAOException {
	 * // TODO Auto-generated method stub return null; }
	 */

	/*
	 * public Collection findSubProgramaWithGestorByUserReport(Long codeUser,
	 * String userGroupName, String codeprograma, Long codeArea) throws
	 * SQLException { // TODO Auto-generated method stub return null; }
	 */

}
